Hi all,
I have a genuine novice question. What's the best "postgres way" to
lock tables in the following work flow circumstances:
A)
1) Begin work;
2) select max(Id) from table;
3) insert into table record with Id=(max+1);
4) commit;
I want to be absolutely certain no other user can run this identical
query concurrently (read the same max(Id)) causing two identical
records to be built with the same Id=(max+1) between steps 2 and 4.
This would require locking the entire table with a "Lock table"
statement between steps 1 and 2, yes? Best syntax?
B)
1) Begin work;
2) Select User from table where Id=n;
3) If User is null then:
Update row Id=n to User="me"
4) commit;
I want to be absolutely certain no other user can update the tuple to
User="not me" between steps 2 and 3. This would require me to add a
"Lock" statement that would prevent reads on this tuple between steps
1 and 2, yes (or a "Select with lock" statment)? Again, a suggestion
for the explicit lock type would be awesome.
I'm especially getting confused by "lock table in row exclusive mode"
without including in this statement which rows to lock...the manual
pages don't offer clear enough examples for this particual newbie.
Thanks!
AB
--
Allan Berger
Bright Eyes & Bushy Tails Veterinary Service
3005 Highway 1 NE
Iowa City, IA 52240
(319) 351-4256 (voice)
(319) 341-8445 (fax) http://www.BEBT.com